<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mybatis.mappers.CategoryMapper">

    <resultMap id="Category" type="Category">
        <result property="id" column="categoryId"/>
        <result property="title" column="title"/>
        <result property="rangeStart" column="leftValue"/>
        <result property="rangeEnd" column="rightValue"/>
        <result property="iconPath" column="iconPath"/>
    </resultMap>
    <resultMap id="DistributorLink" type="models.entities.DistributorLink">
        <result property="id" column="CategoryId"/>
        <result property="distributorId" column="DistributorId"/>
    </resultMap>
    <select id="getByTitle" resultMap="Category">
        select * from categories where title = #{categoryTitle} limit 1;
    </select>
    <select id="get" resultMap="Category">
        select * from categories where categoryId = #{id};
    </select>
    <select id="getCategoryParents" resultMap="Category">
        <![CDATA[

        select * from categories where leftValue < #{rangeStart} and rightValue > #{rangeEnd};
         ]]>
    </select>
    <select id="getCategoryAliases" resultType="models.entities.CategoryAliasEntity">
        select * from category_aliases where categoryId = #{categoryId};
    </select>
    <delete id="deleteCategoryAlias">
        delete from category_aliases where aliasId = #{aliasId};
    </delete>
    <select id="getNestedSet">
        SELECT
        CONCAT( REPEAT( '    ', (COUNT(parent.title) - 1) ), node.title) AS name,
        COUNT(parent.title) as `level`
        FROM categories AS node,
        categories AS parent
        WHERE node.LeftValue BETWEEN parent.LeftValue AND parent.RightValue
        GROUP BY node.title
        ORDER BY node.LeftValue;
    </select>
    <select id="getAll" resultMap="Category">
        SELECT * FROM Categories
    </select>

    <select id="getById" resultMap="Category">


            SELECT
            node.*,
            COUNT(parent.title) as `level`,
            (select count(productId)
                from products_categories where categoryId in
                (select categoryId from categories
                    where leftValue between node.LeftValue and node.RightValue)
            )as productCount
            FROM categories AS node,
            categories AS parent

            WHERE
        <if test="parentId > 0">
            parent.categoryId = #{parentId} and
        </if>
        <if test="parentId == 0">
            parent.categoryId = 2 and
        </if>
            (node.LeftValue BETWEEN parent.LeftValue AND parent.RightValue)

            GROUP BY node.title
            ORDER BY node.LeftValue;

    </select>
    <insert id="addCategoryAlias">
        insert into category_aliases (categoryId, alias) values(#{categoryId}, #{alias});
    </insert>
    <select id="getCategory" resultMap="Category">
        select * from categories where categoryId = #{id};
    </select>
    <delete id="deleteById" parameterType="int">
        DELETE from Categories WHERE CategoryId = #{id};
    </delete>

    <select id="getDistributorCategory" parameterType="java.lang.String" resultMap="Category">
        select * from distributor_categories where distributorId = #{value};
    </select>
    <insert id="linkToDistributor" parameterType="models.entities.CategoryEntity">
        insert into distributor_categories(categoryId, distributorId) values(#{id}, #{distributorId});
    </insert>

    <insert id="create" parameterType="models.entities.CategoryEntity">

        <if test="parentCategoryId == 0">
            <![CDATA[
                insert into categories(Title, LeftValue, RightValue)
                select 'root', 1, 2
                from dual
                where not exists
                (select * from categories where parentCategoryId is null);
                set @categoryId = (Select CategoryId from categories where ParentCategoryId is null order by CategoryId desc LIMIT 1);
             ]]>
        </if>
        <if test="parentCategoryId > 0">
            <![CDATA[
                set @categoryId = #{parentCategoryId};
            ]]>
        </if>
            <![CDATA[

            set @rightVal =
            (Select RightValue - 1
            from
                categories
            where
                CategoryID = @categoryId);

            update categories set RightValue = RightValue + 2 where RightValue > @rightVal;
            update categories set LeftValue = LeftValue + 2 where LeftValue > @rightVal;

            insert into
                categories (title, leftValue, rightValue, ParentCategoryID)
            values (#{title}, @rightVal+1, @rightVal+2, @categoryId);
             ]]>

        <selectKey keyProperty="id" resultType="int" order="AFTER">
            select last_insert_id() as categoryId;
        </selectKey>

    </insert>

    <update id="update" parameterType="models.entities.CategoryEntity">
        UPDATE Categories
        SET
            Title = #{title}
        WHERE categoryId = #{id};
  </update>

</mapper>